linux---postgresql的安装和配置

postgresql的安装：源码安装(推荐)：http://my.oschina.net/hippora/blog/375292
1.tar -xjvf postgresql-9.3.4.tar.bz
2.cd postgresql-9.3.4
3. ./configure（会有错误提示，缺少依赖包readline和zlib）
4. yum install readline-devel和yum install zlib-devel
5.再执行./configure  （后面可跟特定的目录,比如、usr/local/postgresql作为安装目录，没有的话，默认的会在/usr/local/pgsql下）
6.make & make install(执行这一步，postgresql安装完毕，会在/usr/local生成个pgsql目录，里面是postgresql的安装目录)
7.添加用户专门用于postgresql ：useradd postgres 和passwd postgres
8.在/usr/local/pgsql下新建data文件夹，用于放置postgresql的数据文件：mkdir /usr/local/pgsql/data,然后chown -R postgres /usr/local/pgsql/data(一定要属于新建postgres用户)
9.配置环境变量：
su postgres
cd ~
vi .bash_profile
加上：
# postgres
PGDATA=/usr/local/pgsql/data
PATH=/usr/local/pgsql/bin:$PATH
export PGDATA PATH

. .bash_profile （很重要！！）
10.pg_ctl initdb ,然后初始化数据库，在/usr/local/pgsql/data下初始化数据文件和配置文件
11.然后启动pg_ctl start -l /usr/local/pgsql/pgsql.log postgresql服务启动
12.可以通过netstat -tulnp | grep postgres 查看5432端口占用监听情况
13.到这里安装配置结束，还需要修改两个配置：
在/usr/local/pgsql/data下postgresql.conf中端口5432放开，同时listen_addresses = '*'(不然TCP/IP监听不到，客户端远程连接不上postgresql)
在/usr/local/pgsql/data下pg_hba.conf下，添加host all all 192.168.9.0/0 md5 （不然连接不上postgresql）----------用于限定某些机器能连或者不能连postgresql

14.配置结束，通过psql进入postgresql的命令行(最后要注意防火墙是否打开，打开的话要把5432端口放开)

安装过程注意的问题：
1. data的文件夹的属于哪个用户的问题，一定属于postgres
2. postgresql.conf监听的ip一定要放开listen_addresses='*'
3. 基于主机的验证pg_hba.conf中一定要放开你要用的客户端连接postgresql的ip(或者所有ip，生产机上最好不要),验证采用MD5或者ident或者trust
4. 5432防火墙的问题，生产机数据库的话，把5432放开

 

postgresql的备份与恢复：

/usr/local/postgresql-9.3.4/bin/createdb -U postgres 数据库名称 -T template0 新建一个数据库

/usr/local/postgresql-9.3.4/bin/dropdb -U postgres 数据库名称 删除一个数据库

/usr/local/postgresql-9.3.4/bin/psql -U postgres 要恢复的数据库名称 < 备份文件目录路径 用备份文件恢复一个数据库
例子：/usr/local/postgresql-9.3.4/bin/psql -U postgres cloud$1 < /tmp/latest_dbbak/cloud$1

/usr/local/postgresql-9.3.4/bin/pg_dump -U postgres 要恢复的数据库名称 > 备份文件目录路 备份数据库

备份恢复某一张表：
/usr/local/postgresql-9.3.4/bin/psql -U postgres 要恢复的数据库名称 -T 表名 < 备份文件目录路径 用备份文件恢复一个数据库
例子：/usr/local/postgresql-9.3.4/bin/psql -U postgres cloud$1 < /tmp/latest_dbbak/cloud$1

/usr/local/postgresql-9.3.4/bin/pg_dump -U postgres 要恢复的数据库名称 -T 表名> 备份文件目录路 备份数据库

 

进入postgresql命令行：

su postgres,然后psql -U postgres
几个常用的系统表，
select * from pg_shadow;-----所有postgresql的用户 或者\l
select * from pg_database;----所有数据库
select * from pg_tables;-----所有系统表
select * from pg_stat_activity ---所有数据库的进程，可以用于查看某个数据库被哪些引用，然后kill掉
select * from pg_roles --- 所有角色表 或者\du
如何找到postgreSQL数据库中占空间最大的表：
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1
如何计算postgreSQL数据库所占用的硬盘大小
SELECT pg_size_pretty(pg_database_size('数据库名字'))
创建用户或者角色(create user 和create role区别在于默认能不能Login的问题)
CREATE ROLE name LOGIN PASSWORD '123456‘;
CREATE USER name PASSWORD '123456';
删除用户或者角色
DROP ROLE或者DROP USER

创建数据库:
/usr/local/postgresql-9.3.4/bin/createdb -U postgres 数据库名称 -T template0
删除数据库：
/usr/local/postgresql-9.3.4/bin/dropdb -U postgres 数据库

 

下面是常用的pg_hba.conf配置：

#
 TYPE  DATABASE  USER  CIDR-ADDRESS  METHOD
 
#
 "local" is for Unix domain socket connections only
local   
 all      all                 ident
 
#
 IPv4 local connections:
host    
 all      all   127.0.0.1/32  md5
 
#
 IPv6 local connections:
host    
 all      all   ::1/128       md5

　　

TYPE定义了多种连接PostgreSQL的方式，分别是：“local”使用本地unix套接字，“host”使用TCP/IP连接（包括SSL和非SSL），“host”结合“IPv4地址”使用IPv4方式，结合“IPv6地址”则使用IPv6方式，“hostssl”只能使用SSL TCP/IP连接，“hostnossl”不能使用SSL TCP/IP连接。

DATABASE指定哪个数据库，多个数据库，库名间以逗号分隔。“all”只有在没有其他的符合条目时才代表“所有”，如果有其他的符合条目则代表“除了该条之外的”，因为“all”的优先级最低；

USER指定哪个数据库用户（PostgreSQL正规的叫法是角色，role）。多个用户以逗号分隔。

CIDR-ADDRESS项local方式不必填写，该项可以是IPv4地址或IPv6地址，可以定义某台主机或某个网段。

METHOD指定如何处理客户端的认证。常用的有ident，md5，password，trust，reject。

ident是Linux下PostgreSQL默认的local认证方式，凡是能正确登录服务器的操作系统用户（注：不是数据库用户）就能使用本用户映射的数据库用户不需密码登录数据库。用户映射文件为pg_ident.conf，这个文件记录着与操作系统用户匹配的数据库用户，如果某操作系统用户在本文件中没有映射用户，则默认的映射数据库用户与操作系统用户同名。比如，服务器上有名为user1的操作系统用户，同时数据库上也有同名的数据库用户，user1登录操作系统后可以直接输入psql，以user1数据库用户身份登录数据库且不需密码。很多初学者都会遇到psql -U username登录数据库却出现“username ident 认证失败”的错误，明明数据库用户已经createuser。原因就在于此，使用了ident认证方式，却没有同名的操作系统用户或没有相应的映射用户。解决方案：1、在pg_ident.conf中添加映射用户；2、改变认证方式。

md5是常用的密码认证方式，如果你不使用ident，最好使用md5。密码是以md5形式传送给数据库，较安全，且不需建立同名的操作系统用户。

password是以明文密码传送给数据库，建议不要在生产环境中使用。

trust是只要知道数据库用户名就不需要密码或ident就能登录，建议不要在生产环境中使用。

reject是拒绝认证。

　

本地使用psql登录数据库，是以unix套接字的方式，附合local方式。

使用PGAdmin3或php登录数据库，不论是否本地均是以TCP/IP方式，附合host方式。如果是本地（数据库地址localhost），CIDR-ADDRESS则为127.0.0.1/32。

例：

1、允许本地使用PGAdmin3登录数据库，数据库地址localhost，用户user1，数据库user1db：

1	host    user1db    user1    127.0.0.1/32    md5

2、允许10.1.1.0~10.1.1.255网段登录数据库：

1	host    all    all    10.1.1.0/24    md5

3、信任192.168.1.10登录数据库：

1	host    all    all    192.168.1.10/32    trust

　

pg_hba.conf修改后，使用pg_ctl reload重新读取pg_hba.conf文件，如果pg_ctl找不到数据库，则用-D /.../pgsql/data/　指定数据库目录，或export PGDATA=/.../pgsql/data/　导入环境变量。

　

另：PostgreSQL默认只监听本地端口，用netstat -tuln只会看到“tcp 127.0.0.1:5432 LISTEN”。修改postgresql.conf中的listen_address=*，监听所有端口，这样远程才能通过TCP/IP登录数据库，用netstat -tuln会看到“tcp 0.0.0.0:5432